﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InformationSystem.Framework.Dao;
using InformationSystem.BLL.Core.Model;
using Spring.Data.Common;
using System.Data;
using InformationSystem.Utility;
using Spring.Data.Generic;


namespace InformationSystem.BLL.Core.Dao.Impl
{
    public class StudentDaoImpl : InfoSysAdoDaoSupport, IStudentDao
    {
        internal class MarkRow<T> : IRowMapper<T> where T : StudentMark, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T StudentMark = new T();

                StudentMark.RecordID = reader.GetInt32(reader.GetOrdinal("autoid"));
                StudentMark.UserID = reader.GetString(reader.GetOrdinal("userid"));
                StudentMark.UserName = reader.GetString(reader.GetOrdinal("username"));
                StudentMark.CourseID = reader.GetString(reader.GetOrdinal("courseid"));
                StudentMark.CourseName = reader.GetString(reader.GetOrdinal("coursename"));
                StudentMark.CourseSession = reader.GetInt32(reader.GetOrdinal("coursesession"));
                StudentMark.Mark = reader.GetInt32(reader.GetOrdinal("mark"));
                StudentMark.MarkString = reader.GetString(reader.GetOrdinal("markstring"));

                return StudentMark;
            }
        }

        public IList<StudentMark> GetStudentMarkList(string studentid)
        {
            string selectSql = @"select sm.autoid,sm.userid,su.username,sm.courseid,c.coursename,sm.coursesession,sm.mark,sm.markstring 
                                    from StudentMark as sm inner join Course as c on sm.courseid=c.courseid
                                    inner join StudentUser as su on sm.userid=su.userid
                                    where sm.userid=@userid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(studentid);
            return AdoTemplate.QueryWithRowMapper<StudentMark>(CommandType.Text, selectSql, new MarkRow<StudentMark>(), builder.GetParameters());
        }

        public bool ImportStudentMark(IList<StudentMark> stumarklist)
        {
            string selectSql = @"if not exists(
	                        select 1 from StudentMark 
	                        where userid=@userid and courseid=@courseid and coursesession=@coursesession)
	                        insert into StudentMark (userid,courseid,coursesession,markstring) 
	                        values (@userid,@courseid,@coursesession,@markstring)
                            else update StudentMark set markstring=@markstring where userid=@userid and courseid=@courseid and coursesession=@coursesession";
            IDbParametersBuilder builder = null;
            foreach (var item in stumarklist)
            {
                builder = CreateDbParametersBuilder();
                builder.Create().Name("userid").Type(DbType.String).Value(item.UserID);
                builder.Create().Name("courseid").Type(DbType.String).Value(item.CourseID);
                builder.Create().Name("coursesession").Type(DbType.Int32).Value(item.CourseSession);
                builder.Create().Name("markstring").Type(DbType.String).Value(item.MarkString);
                try
                {
                    AdoTemplate.ExecuteNonQuery(CommandType.Text, selectSql, builder.GetParameters());
                }
                catch (Exception)
                {
                    return false;
                }
            }
            return true;
        }

        public bool ImportStudentInfo(StudentInfo stuinfo)
        {
            string modifySql = @"if not exists(
	                        select 1 from StudentUser 
	                        where userid=@userid)
	                        insert into StudentUser (userid,username,loginpwd,sex,collegeid,gradeid,classid,majorName,ppwd) 
	                        values (@userid,@username,@loginpwd,@sex,@collegeid,@gradeid,@classid,@majorName,@ppwd)
                            else update StudentUser set username=@username,sex=@sex,collegeid=@collegeid,gradeid=@gradeid,classid=@classid,majorName=@majorName where userid=@userid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(stuinfo.UserID);
            builder.Create().Name("username").Type(DbType.String).Value(stuinfo.UserName);
            builder.Create().Name("loginpwd").Type(DbType.String).Value(stuinfo.LoginPwd);
            builder.Create().Name("sex").Type(DbType.String).Value(stuinfo.Sex);
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(stuinfo.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(stuinfo.GradeID);
            builder.Create().Name("classid").Type(DbType.String).Value(stuinfo.ClassID);
            builder.Create().Name("majorName").Type(DbType.String).Value(stuinfo.MarjorName);
            builder.Create().Name("ppwd").Type(DbType.String).Value(MSecurity.Encrypt(Common.ParentPre + stuinfo.UserID));
            try
            {
                AdoTemplate.ExecuteNonQuery(CommandType.Text, modifySql, builder.GetParameters());
            }
            catch (Exception ex)
            {
                stuinfo.Msg = ex.Message;
                return false;
            }
            return true;
        }

        internal class StudentInfoRow<T> : IRowMapper<T> where T : StudentInfo, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T StudentInfo = new T();

                StudentInfo.UserID = reader.GetString(reader.GetOrdinal("userid"));
                StudentInfo.UserName = reader.GetString(reader.GetOrdinal("username"));
                StudentInfo.CollegeID = reader.GetInt32(reader.GetOrdinal("collegeid"));
                StudentInfo.CollegeName = reader.GetString(reader.GetOrdinal("collegeName"));
                StudentInfo.GradeID = reader.GetInt32(reader.GetOrdinal("gradeid"));
                StudentInfo.ClassID = reader.GetString(reader.GetOrdinal("classid"));
                //StudentInfo.MajorID = reader.GetString(reader.GetOrdinal("majorid"));
                StudentInfo.MarjorName = reader.GetString(reader.GetOrdinal("majorName"));

                return StudentInfo;
            }
        }

        public bool CreateCourse(Course course)
        {
            string selectSql = @"if not exists(
	                        select 1 from Course 
	                        where courseid=@courseid)
	                        insert into Course (courseid,coursename,courseweight) 
	                        values (@courseid,@coursename,@courseweight)
                            else update Course set coursename=@coursename,courseweight=@courseweight  where courseid=@courseid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();

            builder = CreateDbParametersBuilder();
            builder.Create().Name("courseid").Type(DbType.String).Value(course.CourseID);
            builder.Create().Name("coursename").Type(DbType.String).Value(course.CourseName);
            builder.Create().Name("courseweight").Type(DbType.String).Value(course.CourseWeight);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, selectSql, builder.GetParameters());
            return true;
        }


        public IList<StudentInfo> GetStudentList(string classid)
        {
            string selectSql = @"select su.userid,su.username,su.collegeid,c.collegeName,su.gradeid,su.classid,su.majorName
                                    from StudentUser as su inner join College as c on su.collegeid=c.collegeid                                   
                                    where su.classid=@classid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("classid").Type(DbType.String).Value(classid);
            return AdoTemplate.QueryWithRowMapper<StudentInfo>(CommandType.Text, selectSql, new StudentInfoRow<StudentInfo>(), builder.GetParameters());
        }


        public StudentInfo GetStudentInfo(string studentid)
        {
            string selectSql = @"select su.userid,su.username,su.collegeid,c.collegeName,su.gradeid,su.classid,su.majorName
                                    from StudentUser as su inner join College as c on su.collegeid=c.collegeid                                   
                                    where su.userid=@userid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(studentid);
            return AdoTemplate.QueryForObject<StudentInfo>(CommandType.Text, selectSql, new StudentInfoRow<StudentInfo>(), builder.GetParameters());
        }
    }
}

